import express from 'express'
import cors from 'cors'
import { query } from './config/database.js'
import { createPlayersTable } from './models/player.js'
import { createRoundTypesTable } from './models/roundType.js'
import { createMatchesTable } from './models/match.js'
import { createMatchPlayersTable } from './models/matchPlayer.js'
import { createRoundsTable } from './models/round.js'
import { createDetailedScoresTable } from './models/round.js'
import playerRoutes from './routes/player.js'
import matchRoutes from './routes/match.js'
import roundTypeRoutes from './routes/roundTypeRoutes.js'
import matchPlayerRoutes from './routes/matchPlayerRoutes.js'
import scoreRoutes from './routes/scoreRoutes.js'

const app = express()
app.use(cors())
app.use(express.json())

// 路由
app.use('/api/players', playerRoutes)
app.use('/api/matches', matchRoutes)
app.use('/api/round-types', roundTypeRoutes)
app.use('/api/match-players', matchPlayerRoutes)
app.use('/api', scoreRoutes)

// 学校管理API
app.get('/api/schools', async (req, res) => {
  try {
    const rows = await query('SELECT * FROM schools')
    res.json(rows)
  } catch (error) {
    res.status(500).json({ error: error.message })
  }
})

app.post('/api/schools', async (req, res) => {
  try {
    const { name, short_name, logo_url, region, school_type } = req.body
    const result = await query(
      'INSERT INTO schools (name, short_name, logo_url, region, school_type) VALUES (?, ?, ?, ?, ?)',
      [name, short_name, logo_url, region, school_type]
    )
    res.json({ id: result.insertId })
  } catch (error) {
    res.status(500).json({ error: error.message })
  }
})

app.put('/api/schools/:id', async (req, res) => {
  try {
    const { id } = req.params
    const { name, short_name, logo_url, region, school_type } = req.body
    const result = await query(
      'UPDATE schools SET name = ?, short_name = ?, logo_url = ?, region = ?, school_type = ? WHERE id = ?',
      [name, short_name, logo_url, region, school_type, id]
    )
    res.json({ message: 'School updated successfully' })
  } catch (error) {
    res.status(500).json({ error: error.message })
  }
})

app.delete('/api/schools/:id', async (req, res) => {
  try {
    const { id } = req.params
    const result = await query('DELETE FROM schools WHERE id = ?', [id])
    res.json({ message: 'School deleted successfully' })
  } catch (error) {
    res.status(500).json({ error: error.message })
  }
})

// 赛程管理API
// 1. 比赛表API
app.get('/api/matches', async (req, res) => {
  try {
    const rows = await query(`
      SELECT 
        m.id,
        DATE_FORMAT(m.match_date, '%Y-%m-%d') as match_date,
        TIME_FORMAT(m.match_time, '%H:%i') as match_time,
        m.week_number,
        m.venue_id,
        m.match_schedule_id,
        m.home_school_id,
        m.away_school_id,
        m.home_group_id,
        m.away_group_id,
        m.status,
        m.created_at,
        m.updated_at,
        v.name as venue_name,
        v.location as venue_location,
        hs.id as home_school_id,
        hs.name as home_school_name,
        hs.short_name as home_school_short_name,
        aws.id as away_school_id,
        aws.name as away_school_name,
        aws.short_name as away_school_short_name,
        hg.id as home_group_id,
        hg.group_code as home_group_code,
        hg.grade_level_id as home_grade_level_id,
        ag.id as away_group_id,
        ag.group_code as away_group_code,
        ag.grade_level_id as away_grade_level_id,
        ms.id as schedule_id,
        ms.schedule_name,
        ms.week_number as schedule_week_number,
        hgl.name as home_grade_level_name,
        agl.name as away_grade_level_name
      FROM matches m
      LEFT JOIN venues v ON m.venue_id = v.id
      LEFT JOIN schools hs ON m.home_school_id = hs.id
      LEFT JOIN schools aws ON m.away_school_id = aws.id
      LEFT JOIN schedule_groups hg ON m.home_group_id = hg.id
      LEFT JOIN schedule_groups ag ON m.away_group_id = ag.id
      LEFT JOIN match_schedules ms ON m.match_schedule_id = ms.id
      LEFT JOIN grade_levels hgl ON hg.grade_level_id = hgl.id
      LEFT JOIN grade_levels agl ON ag.grade_level_id = agl.id
      ORDER BY m.match_date ASC, m.match_time ASC
    `)
    res.json(rows)
  } catch (error) {
    console.error('Error:', error)
    res.status(500).json({ error: error.message })
  }
})

app.post('/api/matches', async (req, res) => {
  try {
    const { 
      match_date, 
      match_time, 
      venue_id, 
      match_schedule_id,
      home_school_id,
      away_school_id,
      home_group_id,
      away_group_id,
      status 
    } = req.body

    // 验证主客场学校不能相同
    if (home_school_id === away_school_id) {
      return res.status(400).json({ error: '主客场学校不能相同' })
    }

    const result = await query(
      'INSERT INTO matches (match_date, match_time, venue_id, match_schedule_id, home_school_id, away_school_id, home_group_id, away_group_id, status) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)',
      [match_date, match_time, venue_id, match_schedule_id, home_school_id, away_school_id, home_group_id, away_group_id, status]
    )
    res.json({ id: result.insertId })
  } catch (error) {
    res.status(500).json({ error: error.message })
  }
})

app.put('/api/matches/:id', async (req, res) => {
  try {
    const { id } = req.params
    const { 
      match_date, 
      match_time, 
      venue_id, 
      match_schedule_id,
      home_school_id,
      away_school_id,
      home_group_id,
      away_group_id,
      status 
    } = req.body

    // 验证主客场学校不能相同
    if (home_school_id === away_school_id) {
      return res.status(400).json({ error: '主客场学校不能相同' })
    }

    const result = await query(
      'UPDATE matches SET match_date = ?, match_time = ?, venue_id = ?, match_schedule_id = ?, home_school_id = ?, away_school_id = ?, home_group_id = ?, away_group_id = ?, status = ? WHERE id = ?',
      [match_date, match_time, venue_id, match_schedule_id, home_school_id, away_school_id, home_group_id, away_group_id, status, id]
    )
    res.json({ message: '比赛更新成功' })
  } catch (error) {
    res.status(500).json({ error: error.message })
  }
})

app.delete('/api/matches/:id', async (req, res) => {
  try {
    const { id } = req.params
    const result = await query('DELETE FROM matches WHERE id = ?', [id])
    res.json({ message: '比赛删除成功' })
  } catch (error) {
    res.status(500).json({ error: error.message })
  }
})

// 通过比赛ID获取参赛学校信息
app.get('/api/matches/:id/schools', async (req, res) => {
  try {
    const { id } = req.params
    const rows = await query('SELECT home_school_id, away_school_id FROM matches WHERE id = ?', [id])
    
    if (rows.length === 0) {
      return res.status(404).json({ error: '比赛不存在' })
    }
    
    const [homeSchool] = await query('SELECT * FROM schools WHERE id = ?', [rows[0].home_school_id])
    const [awaySchool] = await query('SELECT * FROM schools WHERE id = ?', [rows[0].away_school_id])
    
    res.json({
      home_school: homeSchool[0],
      away_school: awaySchool[0]
    })
  } catch (error) {
    res.status(500).json({ error: error.message })
  }
})

// 2. 赛程安排表API
app.get('/api/match-schedules', async (req, res) => {
  try {
    const rows = await query(`
      SELECT 
        ms.*,
        gl.name as grade_level_name,
        gl.id as grade_level_id,
        ht.id as home_team_id,
        ht.team_code as home_team_code,
        ht.school_id as home_school_id,
        hts.name as home_school_name,
        hts.short_name as home_school_short_name,
        at.id as away_team_id,
        at.team_code as away_team_code,
        at.school_id as away_school_id,
        ats.name as away_school_name,
        ats.short_name as away_school_short_name,
        (
          SELECT COUNT(*) 
          FROM matches m 
          WHERE m.match_schedule_id = ms.id
        ) as match_count,
        (
          SELECT COUNT(*) 
          FROM matches m 
          WHERE m.match_schedule_id = ms.id AND m.status = 'completed'
        ) as completed_match_count
      FROM match_schedules ms
      LEFT JOIN grade_levels gl ON ms.grade_level_id = gl.id
      LEFT JOIN teams ht ON ms.home_team_id = ht.id
      LEFT JOIN teams at ON ms.away_team_id = at.id
      LEFT JOIN schools hts ON ht.school_id = hts.id
      LEFT JOIN schools ats ON at.school_id = ats.id
      ORDER BY ms.week_number, ms.schedule_name
    `)
    res.json(rows)
  } catch (error) {
    res.status(500).json({ error: error.message })
  }
})

app.get('/api/match-schedules/:id', async (req, res) => {
  try {
    const { id } = req.params
    const rows = await query(`
      SELECT 
        ms.*,
        gl.name as grade_level_name,
        gl.id as grade_level_id,
        ht.id as home_team_id,
        ht.team_code as home_team_code,
        ht.school_id as home_school_id,
        hts.name as home_school_name,
        hts.short_name as home_school_short_name,
        at.id as away_team_id,
        at.team_code as away_team_code,
        at.school_id as away_school_id,
        ats.name as away_school_name,
        ats.short_name as away_school_short_name,
        (
          SELECT COUNT(*) 
          FROM matches m 
          WHERE m.match_schedule_id = ms.id
        ) as match_count,
        (
          SELECT COUNT(*) 
          FROM matches m 
          WHERE m.match_schedule_id = ms.id AND m.status = 'completed'
        ) as completed_match_count
      FROM match_schedules ms
      LEFT JOIN grade_levels gl ON ms.grade_level_id = gl.id
      LEFT JOIN teams ht ON ms.home_team_id = ht.id
      LEFT JOIN teams at ON ms.away_team_id = at.id
      LEFT JOIN schools hts ON ht.school_id = hts.id
      LEFT JOIN schools ats ON at.school_id = ats.id
      WHERE ms.id = ?
    `, [id])
    if (rows.length === 0) {
      return res.status(404).json({ error: '赛程安排不存在' })
    }
    res.json(rows[0])
  } catch (error) {
    res.status(500).json({ error: error.message })
  }
})

app.post('/api/match-schedules', async (req, res) => {
  try {
    const { 
      week_number, 
      schedule_name, 
      grade_level_id, 
      home_team_id, 
      away_team_id 
    } = req.body

    // 验证必填字段
    if (!week_number || !schedule_name || !grade_level_id || !home_team_id || !away_team_id) {
      return res.status(400).json({ error: '缺少必填字段' })
    }

    // 验证主客场队伍不能相同
    if (home_team_id === away_team_id) {
      return res.status(400).json({ error: '主客场队伍不能相同' })
    }

    // 验证队伍是否属于同一组别
    const [teams] = await query(`
      SELECT t1.grade_level_id as home_grade_level, t2.grade_level_id as away_grade_level
      FROM teams t1, teams t2
      WHERE t1.id = ? AND t2.id = ?
    `, [home_team_id, away_team_id])

    if (teams.length === 0) {
      return res.status(400).json({ error: '队伍不存在' })
    }

    if (teams[0].home_grade_level !== teams[0].away_grade_level) {
      return res.status(400).json({ error: '主客场队伍必须属于同一组别' })
    }

    // 验证组别是否正确
    if (teams[0].home_grade_level !== grade_level_id) {
      return res.status(400).json({ error: '队伍组别与赛程组别不匹配' })
    }

    // 创建赛程安排
    const result = await query(
      'INSERT INTO match_schedules (week_number, schedule_name, grade_level_id, home_team_id, away_team_id) VALUES (?, ?, ?, ?, ?)',
      [week_number, schedule_name, grade_level_id, home_team_id, away_team_id]
    )
    res.json({ id: result.insertId })
  } catch (error) {
    res.status(500).json({ error: error.message })
  }
})

app.put('/api/match-schedules/:id', async (req, res) => {
  try {
    const { id } = req.params
    const { 
      week_number, 
      schedule_name, 
      grade_level_id, 
      home_team_id, 
      away_team_id 
    } = req.body

    // 验证必填字段
    if (!week_number || !schedule_name || !grade_level_id || !home_team_id || !away_team_id) {
      return res.status(400).json({ error: '缺少必填字段' })
    }

    // 验证主客场队伍不能相同
    if (home_team_id === away_team_id) {
      return res.status(400).json({ error: '主客场队伍不能相同' })
    }

    // 验证赛程是否存在
    const [existing] = await query('SELECT * FROM match_schedules WHERE id = ?', [id])
    if (existing.length === 0) {
      return res.status(404).json({ error: '赛程安排不存在' })
    }

    // 验证是否有已完成的比赛
    const [matches] = await query(
      'SELECT COUNT(*) as count FROM matches WHERE match_schedule_id = ? AND status = "completed"',
      [id]
    )
    if (matches[0].count > 0) {
      return res.status(400).json({ error: '该赛程已有完成的比赛，不能修改' })
    }

    // 验证队伍是否属于同一组别
    const [teams] = await query(`
      SELECT t1.grade_level_id as home_grade_level, t2.grade_level_id as away_grade_level
      FROM teams t1, teams t2
      WHERE t1.id = ? AND t2.id = ?
    `, [home_team_id, away_team_id])

    if (teams.length === 0) {
      return res.status(400).json({ error: '队伍不存在' })
    }

    if (teams[0].home_grade_level !== teams[0].away_grade_level) {
      return res.status(400).json({ error: '主客场队伍必须属于同一组别' })
    }

    // 验证组别是否正确
    if (teams[0].home_grade_level !== grade_level_id) {
      return res.status(400).json({ error: '队伍组别与赛程组别不匹配' })
    }

    // 更新赛程安排
    const result = await query(
      'UPDATE match_schedules SET week_number = ?, schedule_name = ?, grade_level_id = ?, home_team_id = ?, away_team_id = ? WHERE id = ?',
      [week_number, schedule_name, grade_level_id, home_team_id, away_team_id, id]
    )
    res.json({ message: '赛程安排更新成功' })
  } catch (error) {
    res.status(500).json({ error: error.message })
  }
})

app.delete('/api/match-schedules/:id', async (req, res) => {
  try {
    const { id } = req.params
    // 验证赛程是否存在
    const [existing] = await query('SELECT * FROM match_schedules WHERE id = ?', [id])
    if (existing.length === 0) {
      return res.status(404).json({ error: '赛程安排不存在' })
    }

    // 验证是否有已完成的比赛
    const [matches] = await query(
      'SELECT COUNT(*) as count FROM matches WHERE match_schedule_id = ? AND status = "completed"',
      [id]
    )
    if (matches[0].count > 0) {
      return res.status(400).json({ error: '该赛程已有完成的比赛，不能删除' })
    }

    // 删除赛程安排
    const result = await query('DELETE FROM match_schedules WHERE id = ?', [id])
    res.json({ message: '赛程安排删除成功' })
  } catch (error) {
    res.status(500).json({ error: error.message })
  }
})

// 3. 比赛分组表API
app.get('/api/schedule-groups', async (req, res) => {
  try {
    const rows = await query(`
      SELECT sg.*, gl.name as grade_level_name
      FROM schedule_groups sg
      LEFT JOIN grade_levels gl ON sg.grade_level_id = gl.id
      ORDER BY sg.group_code
    `)
    res.json(rows)
  } catch (error) {
    res.status(500).json({ error: error.message })
  }
})

app.post('/api/schedule-groups', async (req, res) => {
  try {
    const { group_code, grade_level_id } = req.body
    const result = await query(
      'INSERT INTO schedule_groups (group_code, grade_level_id) VALUES (?, ?)',
      [group_code, grade_level_id]
    )
    res.json({ id: result.insertId })
  } catch (error) {
    res.status(500).json({ error: error.message })
  }
})

// 4. 比赛轮次表API
app.get('/api/rounds', async (req, res) => {
  try {
    const rows = await query(`
      SELECT r.*, 
        m.match_date,
        m.match_time,
        ct.name as cube_type_name
      FROM rounds r
      LEFT JOIN matches m ON r.match_id = m.id
      LEFT JOIN cube_types ct ON r.cube_type_id = ct.id
      ORDER BY m.match_date, m.match_time, r.round_number
    `)
    res.json(rows)
  } catch (error) {
    res.status(500).json({ error: error.message })
  }
})

app.post('/api/rounds', async (req, res) => {
  try {
    const { match_id, round_number, cube_type_id } = req.body
    const result = await query(
      'INSERT INTO rounds (match_id, round_number, cube_type_id) VALUES (?, ?, ?)',
      [match_id, round_number, cube_type_id]
    )
    res.json({ id: result.insertId })
  } catch (error) {
    res.status(500).json({ error: error.message })
  }
})

// 5. 成绩表API
app.get('/api/scores', async (req, res) => {
  try {
    const rows = await query(`
      SELECT s.*,
        r.round_number,
        m.match_date,
        m.match_time,
        hp.name as home_player_name,
        ap.name as away_player_name
      FROM scores s
      LEFT JOIN rounds r ON s.round_id = r.id
      LEFT JOIN matches m ON r.match_id = m.id
      LEFT JOIN players hp ON s.home_player_id = hp.id
      LEFT JOIN players ap ON s.away_player_id = ap.id
      ORDER BY m.match_date, m.match_time, r.round_number
    `)
    res.json(rows)
  } catch (error) {
    res.status(500).json({ error: error.message })
  }
})

app.post('/api/scores', async (req, res) => {
  try {
    const { 
      round_id, 
      home_player_id, 
      away_player_id, 
      home_score, 
      away_score,
      home_unsolved,
      away_unsolved,
      home_penalty,
      away_penalty,
      winner_id 
    } = req.body

    // 验证主客场选手不能相同
    if (home_player_id === away_player_id) {
      return res.status(400).json({ error: '主客场选手不能相同' })
    }

    const result = await query(
      'INSERT INTO scores (round_id, home_player_id, away_player_id, home_score, away_score, home_unsolved, away_unsolved, home_penalty, away_penalty, winner_id) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)',
      [round_id, home_player_id, away_player_id, home_score, away_score, home_unsolved, away_unsolved, home_penalty, away_penalty, winner_id]
    )
    res.json({ id: result.insertId })
  } catch (error) {
    res.status(500).json({ error: error.message })
  }
})

// 获取所有选手
app.get('/api/players', async (req, res) => {
  try {
    console.log('开始获取选手列表')
    const [players] = await query(`
      SELECT 
        p.*,
        s.name as school_name,
        s.short_name as school_short_name,
        s.region as school_region,
        s.school_type,
        gl.name as grade_level_name
      FROM players p
      LEFT JOIN schools s ON p.school_id = s.id
      LEFT JOIN grade_levels gl ON p.grade_level_id = gl.id
      ORDER BY p.name
    `)
    
    // 转换数据格式
    const formattedPlayers = players.map(player => {
      const { 
        school_name, school_short_name, school_region, school_type, grade_level_name,
        ...playerData 
      } = player
      
      return {
        ...playerData,
        school: {
          id: player.school_id,
          name: school_name || '',
          short_name: school_short_name || '',
          region: school_region || '',
          school_type: school_type || ''
        },
        grade_level: {
          id: player.grade_level_id,
          name: grade_level_name || ''
        }
      }
    })
    
    // 添加调试日志
    console.log('SQL查询结果:', players[0])
    console.log('转换后的数据:', formattedPlayers[0])
    
    res.json(formattedPlayers)
  } catch (error) {
    console.error('获取选手列表失败:', error)
    res.status(500).json({ error: '获取选手列表失败' })
  }
})

app.post('/api/players', async (req, res) => {
  try {
    const { name, school_id, grade_level_id, gender, birth_date, photo_url } = req.body
    const result = await query(
      'INSERT INTO players (name, school_id, grade_level_id, gender, birth_date, photo_url) VALUES (?, ?, ?, ?, ?, ?)',
      [name, school_id, grade_level_id, gender, birth_date, photo_url]
    )
    res.json({ id: result.insertId })
  } catch (error) {
    res.status(500).json({ error: error.message })
  }
})

app.put('/api/players/:id', async (req, res) => {
  try {
    const { id } = req.params
    const { name, school_id, grade_level_id, gender, birth_date, photo_url } = req.body
    const result = await query(
      'UPDATE players SET name = ?, school_id = ?, grade_level_id = ?, gender = ?, birth_date = ?, photo_url = ? WHERE id = ?',
      [name, school_id, grade_level_id, gender, birth_date, photo_url, id]
    )
    res.json({ message: 'Player updated successfully' })
  } catch (error) {
    res.status(500).json({ error: error.message })
  }
})

app.delete('/api/players/:id', async (req, res) => {
  try {
    const { id } = req.params
    const result = await query('DELETE FROM players WHERE id = ?', [id])
    res.json({ message: 'Player deleted successfully' })
  } catch (error) {
    res.status(500).json({ error: error.message })
  }
})

// 获取辅助数据API
app.get('/api/venues', async (req, res) => {
  try {
    const rows = await query('SELECT * FROM venues')
    res.json(rows)
  } catch (error) {
    res.status(500).json({ error: error.message })
  }
})

app.get('/api/cube-types', async (req, res) => {
  try {
    const rows = await query('SELECT * FROM cube_types')
    res.json(rows)
  } catch (error) {
    res.status(500).json({ error: error.message })
  }
})

app.get('/api/grade-levels', async (req, res) => {
  try {
    const rows = await query('SELECT * FROM grade_levels')
    res.json(rows)
  } catch (error) {
    res.status(500).json({ error: error.message })
  }
})

// 获取队伍列表API
app.get('/api/teams', async (req, res) => {
  try {
    const rows = await query('SELECT * FROM teams')
    res.json(rows)
  } catch (error) {
    res.status(500).json({ error: error.message })
  }
})

// 获取单个比赛详情
app.get('/api/matches/:id', async (req, res) => {
  try {
    const { id } = req.params
    const rows = await query(`
      SELECT 
        m.id,
        DATE_FORMAT(m.match_date, '%Y-%m-%d') as match_date,
        TIME_FORMAT(m.match_time, '%H:%i') as match_time,
        m.week_number,
        m.venue_id,
        m.match_schedule_id,
        m.home_school_id,
        m.away_school_id,
        m.home_group_id,
        m.away_group_id,
        m.status,
        m.created_at,
        m.updated_at,
        v.name as venue_name,
        v.location as venue_location,
        hs.id as home_school_id,
        hs.name as home_school_name,
        hs.short_name as home_school_short_name,
        aws.id as away_school_id,
        aws.name as away_school_name,
        aws.short_name as away_school_short_name,
        hg.id as home_group_id,
        hg.group_code as home_group_code,
        hg.grade_level_id as home_grade_level_id,
        ag.id as away_group_id,
        ag.group_code as away_group_code,
        ag.grade_level_id as away_grade_level_id,
        ms.id as schedule_id,
        ms.schedule_name,
        ms.week_number as schedule_week_number,
        hgl.name as home_grade_level_name,
        agl.name as away_grade_level_name
      FROM matches m
      LEFT JOIN venues v ON m.venue_id = v.id
      LEFT JOIN schools hs ON m.home_school_id = hs.id
      LEFT JOIN schools aws ON m.away_school_id = aws.id
      LEFT JOIN schedule_groups hg ON m.home_group_id = hg.id
      LEFT JOIN schedule_groups ag ON m.away_group_id = ag.id
      LEFT JOIN match_schedules ms ON m.match_schedule_id = ms.id
      LEFT JOIN grade_levels hgl ON hg.grade_level_id = hgl.id
      LEFT JOIN grade_levels agl ON ag.grade_level_id = agl.id
      WHERE m.id = ?
    `, [id])
    
    if (rows.length === 0) {
      return res.status(404).json({ error: '比赛不存在' })
    }
    
    res.json(rows[0])
  } catch (error) {
    console.error('Error fetching match details:', error)
    res.status(500).json({ error: error.message })
  }
})

// 获取比赛选手列表
app.get('/api/matches/:id/players', async (req, res) => {
  try {
    const { id } = req.params
    const rows = await query('SELECT home_school_id, away_school_id, home_group_id, away_group_id FROM matches WHERE id = ?', [id])
    
    if (rows.length === 0) {
      return res.status(404).json({ error: '比赛不存在' })
    }
    
    const match = rows[0];

    // 获取选手信息，根据组别ID筛选
    const [players] = await query(`
      SELECT p.*, s.name as school_name 
       FROM players p
       LEFT JOIN schools s ON p.school_id = s.id
       WHERE p.school_id IN (?, ?) 
       AND p.grade_level_id IN (
         SELECT grade_level_id FROM schedule_groups WHERE id IN (?, ?)
       )`,
      [match.home_school_id, match.away_school_id, match.home_group_id, match.away_group_id]
    )
    
    res.json(players)
  } catch (error) {
    res.status(500).json({ error: error.message })
  }
})

// 获取比赛场次的学校名称和组别信息
app.get('/api/match-schedules/:id/schools', async (req, res) => {
  try {
    const { id } = req.params
    const rows = await query(`
      SELECT 
        ms.id as match_schedule_id,
        ms.schedule_name,
        ms.week_number,
        ht.team_code as home_team_code,
        at.team_code as away_team_code,
        hts.name as home_school_name,
        hts.short_name as home_school_short_name,
        ats.name as away_school_name,
        ats.short_name as away_school_short_name,
        hg.group_code as home_group_code,
        ag.group_code as away_group_code
      FROM match_schedules ms
      LEFT JOIN teams ht ON ms.home_team_id = ht.id
      LEFT JOIN teams at ON ms.away_team_id = at.id
      LEFT JOIN schools hts ON ht.school_id = hts.id
      LEFT JOIN schools ats ON at.school_id = ats.id
      LEFT JOIN schedule_groups hg ON ht.group_id = hg.id
      LEFT JOIN schedule_groups ag ON at.group_id = ag.id
      WHERE ms.id = ?
    `, [id])
    
    if (rows.length === 0) {
      return res.status(404).json({ error: '赛程安排不存在' })
    }
    
    res.json(rows[0])
  } catch (error) {
    res.status(500).json({ error: error.message })
  }
})

// 获取回合类型列表
app.get('/api/round-types', async (req, res) => {
  try {
    const rows = await query(`
      SELECT 
        id,
        name,
        description,
        created_at,
        updated_at
      FROM round_types
    `)
    res.json(rows)
  } catch (error) {
    res.status(500).json({ error: error.message })
  }
})

// 获取比赛项目信息
app.get('/api/events', async (req, res) => {
  try {
    const rows = await query(`
      SELECT 
        ct.id,
        ct.name,
        ct.created_at,
        ct.updated_at,
        (
          SELECT COUNT(DISTINCT r.id)
          FROM rounds r
          WHERE r.cube_type_id = ct.id
        ) as total_rounds,
        (
          SELECT COUNT(DISTINCT s.id)
          FROM rounds r
          LEFT JOIN scores s ON s.round_id = r.id
          WHERE r.cube_type_id = ct.id
        ) as total_matches
      FROM cube_types ct
      ORDER BY ct.id
    `)
    res.json(rows)
  } catch (error) {
    res.status(500).json({ error: error.message })
  }
})

// 获取比赛可选择的选手
app.get('/api/matches/:matchId/available-players', async (req, res) => {
  try {
    console.log('开始获取可选择选手，比赛ID:', req.params.matchId)
    
    // 获取比赛信息和相关组别信息
    const matchRows = await query(`
      SELECT 
        m.*,
        hg.grade_level_id as home_grade_level_id,
        ag.grade_level_id as away_grade_level_id,
        hg.group_code as home_group_code,
        ag.group_code as away_group_code,
        hgl.name as home_grade_level_name,
        agl.name as away_grade_level_name,
        hs.id as home_school_id,
        hs.name as home_school_name,
        aws.id as away_school_id,
        aws.name as away_school_name
      FROM matches m
      LEFT JOIN schedule_groups hg ON m.home_group_id = hg.id
      LEFT JOIN schedule_groups ag ON m.away_group_id = ag.id
      LEFT JOIN grade_levels hgl ON hg.grade_level_id = hgl.id
      LEFT JOIN grade_levels agl ON ag.grade_level_id = agl.id
      LEFT JOIN schools hs ON m.home_school_id = hs.id
      LEFT JOIN schools aws ON m.away_school_id = aws.id
      WHERE m.id = ?
    `, [req.params.matchId])
    
    console.log('获取到的比赛信息:', matchRows)
    
    if (!matchRows || matchRows.length === 0) {
      console.log('未找到比赛信息')
      return res.status(404).json({ error: '比赛不存在' })
    }
    
    const matchInfo = matchRows[0];
    
    // 检查必要字段是否存在
    if (!matchInfo.home_school_id || !matchInfo.away_school_id || 
        !matchInfo.home_grade_level_id || !matchInfo.away_grade_level_id) {
      console.error('比赛信息不完整:', matchInfo)
      return res.status(500).json({ 
        error: '比赛信息不完整，缺少必要的学校或组别信息',
        missingFields: {
          home_school_id: !matchInfo.home_school_id,
          away_school_id: !matchInfo.away_school_id,
          home_grade_level_id: !matchInfo.home_grade_level_id,
          away_grade_level_id: !matchInfo.away_grade_level_id
        }
      })
    }

    // 获取已经抽签的选手ID
    const drawnPlayers = await query(
      'SELECT player_id FROM draw_results WHERE match_id = ?',
      [req.params.matchId]
    )
    
    console.log('已抽签的选手:', drawnPlayers)
    
    const drawnPlayerIds = drawnPlayers && drawnPlayers.length > 0 ? drawnPlayers.map(dp => dp.player_id) : []
    console.log('已抽签的选手ID列表:', drawnPlayerIds)
    
    // 构建排除条件
    const excludeCondition = drawnPlayerIds.length > 0 
      ? `AND p.id NOT IN (${drawnPlayerIds.join(',')})` 
      : ''
    
    console.log('排除条件:', excludeCondition)
    
    // 获取主场可选择的选手
    const homeQuery = `
      SELECT DISTINCT
        p.id,
        p.name,
        p.school_id,
        s.name as school_name,
        gl.id as grade_level_id,
        gl.name as grade_level
      FROM players p
      JOIN schools s ON p.school_id = s.id
      JOIN grade_levels gl ON p.grade_level_id = gl.id
      WHERE p.school_id = ?
      AND gl.id = ?
      ${excludeCondition}
      ORDER BY p.name
    `
    console.log('主场选手查询SQL:', homeQuery)
    console.log('主场查询参数:', [matchInfo.home_school_id, matchInfo.home_grade_level_id])
    
    let homePlayers = [];
    try {
      const homePlayersResult = await query(homeQuery, [matchInfo.home_school_id, matchInfo.home_grade_level_id]);
      homePlayers = homePlayersResult || [];
      console.log('获取到的主场选手:', homePlayers)
    } catch (queryError) {
      console.error('查询主场选手失败:', queryError);
      // 继续执行，不中断整个请求
    }
    
    // 获取客场可选择的选手
    const awayQuery = `
      SELECT DISTINCT
        p.id,
        p.name,
        p.school_id,
        s.name as school_name,
        gl.id as grade_level_id,
        gl.name as grade_level
      FROM players p
      JOIN schools s ON p.school_id = s.id
      JOIN grade_levels gl ON p.grade_level_id = gl.id
      WHERE p.school_id = ?
      AND gl.id = ?
      ${excludeCondition}
      ORDER BY p.name
    `
    console.log('客场选手查询SQL:', awayQuery)
    console.log('客场查询参数:', [matchInfo.away_school_id, matchInfo.away_grade_level_id])
    
    let awayPlayers = [];
    try {
      const awayPlayersResult = await query(awayQuery, [matchInfo.away_school_id, matchInfo.away_grade_level_id]);
      awayPlayers = awayPlayersResult || [];
      console.log('获取到的客场选手:', awayPlayers)
    } catch (queryError) {
      console.error('查询客场选手失败:', queryError);
      // 继续执行，不中断整个请求
    }
    
    // 获取组别信息
    let homeGroup = [];
    try {
      const homeGroupResult = await query(`
        SELECT sg.*, gl.name as grade_level_name
        FROM schedule_groups sg
        JOIN grade_levels gl ON sg.grade_level_id = gl.id
        WHERE sg.id = ?
      `, [matchInfo.home_group_id]);
      homeGroup = homeGroupResult;
      console.log('主场组别信息:', homeGroup);
    } catch (queryError) {
      console.error('查询主场组别失败:', queryError);
      // 继续执行，不中断整个请求
    }

    let awayGroup = [];
    try {
      const awayGroupResult = await query(`
        SELECT sg.*, gl.name as grade_level_name
        FROM schedule_groups sg
        JOIN grade_levels gl ON sg.grade_level_id = gl.id
        WHERE sg.id = ?
      `, [matchInfo.away_group_id]);
      awayGroup = awayGroupResult;
      console.log('客场组别信息:', awayGroup);
    } catch (queryError) {
      console.error('查询客场组别失败:', queryError);
      // 继续执行，不中断整个请求
    }
    
    const response = {
      group: {
        home_group: homeGroup && homeGroup.length > 0 ? {
          id: homeGroup[0].id,
          code: homeGroup[0].group_code,
          grade_level_id: homeGroup[0].grade_level_id,
          grade_level_name: homeGroup[0].grade_level_name
        } : null,
        away_group: awayGroup && awayGroup.length > 0 ? {
          id: awayGroup[0].id,
          code: awayGroup[0].group_code,
          grade_level_id: awayGroup[0].grade_level_id,
          grade_level_name: awayGroup[0].grade_level_name
        } : null
      },
      players: {
        home_players: homePlayers || [],
        away_players: awayPlayers || []
      }
    }
    
    console.log('返回的完整响应:', response)
    res.json(response)
  } catch (error) {
    console.error('获取可用选手失败，详细错误:', error)
    console.error('错误堆栈:', error.stack)
    res.status(500).json({ error: error.message })
  }
})

// 获取抽签结果
app.get('/api/matches/:matchId/draw-results', async (req, res) => {
  try {
    const rows = await query(`
      SELECT 
        dr.id,
        dr.match_id,
        dr.player_id,
        dr.draw_number,
        dr.seat_number,
        p.name as player_name,
        p.school_id,
        s.name as school_name,
        gl.name as grade_level,
        p.grade_level_id
      FROM draw_results dr
      JOIN players p ON dr.player_id = p.id
      JOIN schools s ON p.school_id = s.id
      JOIN grade_levels gl ON p.grade_level_id = gl.id
      WHERE dr.match_id = ?
      ORDER BY dr.draw_number
    `, [req.params.matchId])
    
    res.json(rows)
  } catch (error) {
    res.status(500).json({ error: error.message })
  }
})

// 添加抽签结果
app.post('/api/matches/:matchId/draw-results', async (req, res) => {
  try {
    const { matchId } = req.params;
    const { player_id, draw_number, seat_number } = req.body;
    
    // 验证必填字段
    if (!player_id || !draw_number) {
      return res.status(400).json({ error: '选手ID和抽签号为必填项' });
    }

    // 检查比赛是否存在
    const matches = await query('SELECT * FROM matches WHERE id = ?', [matchId]);
    if (matches.length === 0) {
      return res.status(404).json({ error: '比赛不存在' });
    }

    // 检查该选手是否已经抽签
    const existingDraws = await query(
      'SELECT * FROM draw_results WHERE match_id = ? AND player_id = ?',
      [matchId, player_id]
    );
    
    if (existingDraws.length > 0) {
      return res.status(400).json({ error: '该选手已经抽签' });
    }

    // 获取当前选手的学校ID
    const playerData = await query('SELECT school_id FROM players WHERE id = ?', [player_id]);
    if (playerData.length === 0) {
      return res.status(404).json({ error: '选手不存在' });
    }
    const schoolId = playerData[0].school_id;

    // 检查该抽签号是否已被同一学校的其他选手使用
    const existingDrawNumbers = await query(`
      SELECT dr.* 
      FROM draw_results dr
      JOIN players p ON dr.player_id = p.id
      WHERE dr.match_id = ? 
      AND dr.draw_number = ?
      AND p.school_id = ?`,
      [matchId, draw_number, schoolId]
    );
    
    if (existingDrawNumbers.length > 0) {
      return res.status(400).json({ error: '该抽签号已被同一学校的其他选手使用' });
    }

    // 添加抽签结果
    const result = await query(
      'INSERT INTO draw_results (match_id, player_id, draw_number, seat_number) VALUES (?, ?, ?, ?)',
      [matchId, player_id, draw_number, seat_number || draw_number] // 如果座位号未提供，使用抽签号作为座位号
    );
    
    res.status(201).json({ 
      id: result.insertId,
      match_id: parseInt(matchId),
      player_id,
      draw_number,
      seat_number: seat_number || draw_number,
      message: '抽签结果添加成功'
    });
  } catch (error) {
    console.error('添加抽签结果失败:', error);
    res.status(500).json({ error: `添加抽签结果失败: ${error.message}` });
  }
});

// 删除单个抽签结果
app.delete('/api/matches/:matchId/draw-results/:drawResultId', async (req, res) => {
  try {
    const { matchId, drawResultId } = req.params;
    
    // 验证抽签结果存在且属于该比赛
    const drawResults = await query(
      'SELECT * FROM draw_results WHERE id = ? AND match_id = ?',
      [drawResultId, matchId]
    );
    
    if (drawResults.length === 0) {
      return res.status(404).json({ error: '抽签结果不存在或不属于当前比赛' });
    }
    
    // 删除抽签结果
    await query('DELETE FROM draw_results WHERE id = ?', [drawResultId]);
    
    res.json({ message: '抽签结果删除成功' });
  } catch (error) {
    console.error('删除抽签结果失败:', error);
    res.status(500).json({ error: `删除抽签结果失败: ${error.message}` });
  }
});

// 确认抽签并生成对战记录
app.post('/api/matches/:matchId/confirm-draw', async (req, res) => {
  const { matchId } = req.params;

  try {
    // 1. 获取比赛信息
    const matches = await query(`
      SELECT m.*, 
             hg.grade_level_id as home_grade_level_id,
             ag.grade_level_id as away_grade_level_id
      FROM matches m
      LEFT JOIN schedule_groups hg ON m.home_group_id = hg.id
      LEFT JOIN schedule_groups ag ON m.away_group_id = ag.id
      WHERE m.id = ?`, 
      [matchId]
    );
    
    if (matches.length === 0) {
      return res.status(404).json({ error: '比赛不存在' });
    }
    
    const match = matches[0];
    
    // 检查必要的关联数据是否存在
    if (!match.home_school_id || !match.away_school_id || 
        !match.home_grade_level_id || !match.away_grade_level_id) {
      return res.status(400).json({ error: '比赛信息不完整，缺少学校或组别信息' });
    }
    
    // 2. 根据比赛的学校和组别获取参赛选手
    const players = await query(`
      SELECT p.*
      FROM players p
      WHERE (p.school_id = ? AND p.grade_level_id = ?) 
         OR (p.school_id = ? AND p.grade_level_id = ?)
      ORDER BY p.school_id, p.name`,
      [
        match.home_school_id, match.home_grade_level_id,
        match.away_school_id, match.away_grade_level_id
      ]
    );
    
    if (players.length === 0) {
      return res.status(400).json({ error: '未找到符合条件的参赛选手' });
    }
    
    // 3. 获取所有魔方类型
    const cubeTypes = await query('SELECT * FROM cube_types');
    
    if (cubeTypes.length === 0) {
      return res.status(400).json({ error: '未找到魔方类型数据' });
    }
    
    // 4. 删除该比赛已存在的match_scores和match_tables记录(如果有)
    await query('DELETE FROM match_scores WHERE match_id = ?', [matchId]);
    await query('DELETE FROM match_tables WHERE match_id = ?', [matchId]);
    
    // 5. 获取已经抽签的选手信息和抽签号
    const drawResults = await query(`
      SELECT 
        player_id,
        draw_number
      FROM draw_results
      WHERE match_id = ?
    `, [matchId]);
    
    // 创建选手抽签号映射
    const playerDrawNumbers = {};
    drawResults.forEach(result => {
      playerDrawNumbers[result.player_id] = result.draw_number;
    });
    
    // 6. 为每位选手生成所有魔方类型和轮次的成绩记录
    let matchScoresCount = 0;
    const homePlayerCount = players.filter(p => p.school_id === match.home_school_id).length;
    const awayPlayerCount = players.filter(p => p.school_id === match.away_school_id).length;
    
    for (const player of players) {
      // 获取选手的抽签号
      const drawNumber = playerDrawNumbers[player.id] || null;
      
      for (const cubeType of cubeTypes) {
        for (let roundNumber = 1; roundNumber <= 3; roundNumber++) {
          // 创建成绩记录，填入抽签号
          await query(
            `INSERT INTO match_scores
             (match_id, player_id, cube_type_id, round_number, draw_number, score, is_completed, is_confirmed)
             VALUES (?, ?, ?, ?, ?, NULL, 0, 0)`,
            [
              matchId,
              player.id,
              cubeType.id,
              roundNumber,
              drawNumber // 添加抽签号
            ]
          );
          matchScoresCount++;
        }
      }
    }
    
    // 7. 更新比赛状态为"已准备"
    await query(
      'UPDATE matches SET status = ? WHERE id = ?',
      ['prepared', matchId]
    );
    
    // 返回成功消息及统计信息
    res.json({
      message: '比赛成绩记录生成成功',
      stats: {
        players: players.length,
        homePlayerCount: homePlayerCount,
        awayPlayerCount: awayPlayerCount,
        cubeTypes: cubeTypes.length,
        roundsPerCubeType: 3,
        matchScoresCreated: matchScoresCount,
        recordsPerPlayer: cubeTypes.length * 3, // 每位选手的记录数
        playersWithDrawNumbers: drawResults.length // 有抽签号的选手数
      }
    });
  } catch (error) {
    console.error('生成比赛成绩记录失败:', error);
    res.status(500).json({ error: '生成比赛成绩记录失败: ' + error.message });
  }
});

// 获取比赛的所有轮次
app.get('/api/matches/:matchId/rounds', async (req, res) => {
  const { matchId } = req.params;
  let connection;

  try {
    connection = await query('SELECT * FROM rounds WHERE match_id = ?', [matchId])
    res.json(connection)
  } catch (error) {
    console.error('获取轮次失败:', error);
    res.status(500).json({ error: '获取轮次失败' });
  } finally {
    if (connection) {
      connection.end()
    }
  }
});

// 批量创建成绩记录
app.post('/api/matches/:matchId/match-scores/batch', async (req, res) => {
  const { matchId } = req.params;
  const { matchScores } = req.body;
  let connection;

  try {
    connection = await query('SELECT * FROM matches WHERE id = ?', [matchId])
    
    if (connection.length === 0) {
      return res.status(404).json({ error: '比赛不存在' })
    }

    // 删除该比赛已存在的成绩记录
    await query(
      'DELETE FROM match_scores WHERE match_id = ?',
      [matchId]
    );

    // 批量插入新的成绩记录
    if (matchScores && matchScores.length > 0) {
      const values = matchScores.map(score => [
        score.match_id,
        score.player_id,
        score.round_id,
        score.cube_type_id,
        score.score,
        score.is_solved
      ]);

      await query(
        `INSERT INTO match_scores 
        (match_id, player_id, round_id, cube_type_id, score, is_solved) 
        VALUES ?`,
        [values]
      );
    }

    res.json({ message: '成绩记录创建成功' });
  } catch (error) {
    console.error('创建成绩记录失败:', error);
    res.status(500).json({ error: '创建成绩记录失败' });
  } finally {
    if (connection) {
      connection.end()
    }
  }
});

// 获取比赛的参与选手
app.get('/api/matches/:id/players', async (req, res) => {
  try {
    const { id } = req.params
    const rows = await query(`
      SELECT DISTINCT p.id, p.name, s.id as school_id, s.name as school_name
      FROM players p
      JOIN schools s ON p.school_id = s.id
      JOIN match_players mp ON p.id = mp.player_id
      WHERE mp.match_id = ?
      ORDER BY p.name
    `, [id])
    res.json(rows)
  } catch (error) {
    res.status(500).json({ error: error.message })
  }
})

// 代理成绩管理相关API - 确保scoreRoutes中的API可以被正确访问
app.get('/api/matches/:id/scores/rounds', async (req, res) => {
  try {
    const matchId = req.params.id;
    
    const rounds = await query(
      'SELECT DISTINCT round_number FROM match_scores WHERE match_id = ? ORDER BY round_number',
      [matchId]
    );
    
    res.json(rounds.map(r => r.round_number));
  } catch (error) {
    console.error('Error fetching rounds:', error);
    res.status(500).json({ error: '获取轮次列表失败' });
  }
});

app.get('/api/matches/:id/scores/cube-types', async (req, res) => {
  try {
    const matchId = req.params.id;
    
    const cubeTypes = await query(
      `SELECT DISTINCT ct.id, ct.name 
       FROM match_scores ms
       JOIN cube_types ct ON ms.cube_type_id = ct.id
       WHERE ms.match_id = ?`,
      [matchId]
    );
    
    res.json(cubeTypes);
  } catch (error) {
    console.error('Error fetching cube types:', error);
    res.status(500).json({ error: '获取魔方类型列表失败' });
  }
});

// 初始化数据库
const initDatabase = async () => {
  try {
    await createPlayersTable()
    await createRoundTypesTable()
    await createMatchesTable()
    await createMatchPlayersTable()
    await createRoundsTable()
    await createDetailedScoresTable()
    console.log('所有数据库表初始化完成')
  } catch (error) {
    console.error('数据库表初始化失败:', error)
    process.exit(1)
  }
}

// 启动服务器
const PORT = process.env.PORT || 3000
app.listen(PORT, async () => {
  await initDatabase()
  console.log(`服务器运行在端口 ${PORT}`)
})